package Student.Dao;


import Student.Entity.Student;
import Student.Kit.DBUtil;

import java.io.Serializable;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class StudentDao implements Serializable {
    //增
    public int add(Student s) {
        Connection con = DBUtil.getConnection();
        PreparedStatement ps = null;
        String sql = "insert into s_student(sname,sex,telephone,qq,wechat,gradeid,enterdate,fromcity,birthday,address,pass,idcardpic) values(?,?,?,?,?,?,?,?,?,?,?,?)";
        try {
            ps = con.prepareStatement(sql);
            ps.setString(1, s.getSname());
            ps.setString(2, s.getSex());
            ps.setString(3, s.getTelephone());
            ps.setString(4, s.getQq());
            ps.setString(5, s.getWechat());
            ps.setInt(6, s.getGradeid());
            ps.setDate(7, s.getEnterdate());
            ps.setString(8, s.getFromcity());
            ps.setDate(9, s.getBirthday());
            ps.setString(10, s.getAddress());
            ps.setString(11, s.getPass());
            ps.setString(12, s.getIdcardpic());
            return ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(ps);
            DBUtil.close(con);
        }
        return 0;
    }

    //改
    public int update(Student s) {
        Connection con = DBUtil.getConnection();
        PreparedStatement ps = null;
        String sql = "update s_student set sname=?,sex=?,telephone=?,qq=?,wechat=?,gradeid=?,enterdate=?,fromcity=?,birthday=?,address=?,pass=?,idcardpic=? where stuno=?";
        try {
            ps = con.prepareStatement(sql);
            ps.setString(1, s.getSname());
            ps.setString(2, s.getSex());
            ps.setString(3, s.getTelephone());
            ps.setString(4, s.getQq());
            ps.setString(5, s.getWechat());
            ps.setInt(6, s.getGradeid());
            ps.setDate(7, s.getEnterdate());
            ps.setString(8, s.getFromcity());
            ps.setDate(9, s.getBirthday());
            ps.setString(10, s.getAddress());
            ps.setString(11, s.getPass());
            ps.setString(12, s.getIdcardpic());
            ps.setLong(13, s.getStuno());
            return ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(ps);
            DBUtil.close(con);
        }
        return 0;
    }

    //删
    public int delete(int stuno) {
        Connection con = DBUtil.getConnection();
        String sql = "delete from s_student where stuno=?";
        PreparedStatement ps = null;
        try {
            ps = con.prepareStatement(sql);
            ps.setInt(1, stuno);
            return ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(ps);
            DBUtil.close(con);
        }
        return 0;
    }

    //根据主键查对象
    public Student find(int stuno) {
        Connection con = DBUtil.getConnection();
        String sql = "select stuno,sname,sex,telephone,qq,wechat,gradeid,enterdate,fromcity,birthday,address,pass,idcardpic from s_student where stuno=?";
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = con.prepareStatement(sql);
            ps.setInt(1, stuno);
            rs = ps.executeQuery();
            if (rs.next()) {
                return new Student(rs.getInt(1), rs.getString(2), rs.getString(3),
                        rs.getString(4), rs.getString(5), rs.getString(6),
                        rs.getInt(7), rs.getDate(8), rs.getString(9),
                        rs.getDate(10), rs.getString(11), rs.getString(12),
                        rs.getString(13));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(rs, ps, con);
        }
        return null;
    }

    //查全部
    public List<Student> search(int page, int line) {
        Connection con = DBUtil.getConnection();
        String sql = "select stuno,sname,sex,telephone,qq,wechat,gradeid,enterdate,fromcity,birthday,address,pass,idcardpic from s_student limit ?,?";
        List<Student> list = new ArrayList<>();
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = con.prepareStatement(sql);
            ps.setInt(1, page);
            ps.setInt(2, line);
            rs = ps.executeQuery();
            while (rs.next()) {
                list.add(new Student(rs.getInt("stuno"), rs.getString("sname"), rs.getString("sex"),
                        rs.getString("telephone"), rs.getString("qq"), rs.getString("wechat"),
                        rs.getInt("gradeid"), rs.getDate("enterdate"), rs.getString("fromcity"),
                        rs.getDate("birthday"), rs.getString("address"), rs.getString("pass"),
                        rs.getString("idcardpic")));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(rs, ps, con);
        }
        return list;
    }
}
